<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Reports extends CI_Controller {
	
	function __construct()
	{
		parent::__construct();
		
		$this->load->library('ion_auth');
		$this->load->library('form_validation');
		$this->load->library('session');
		$this->load->library('pagination');
		$this->load->database();
		$this->lang->load('auth');
		$this->lang->load('admin_estate');
		$this->load->helper('language');
		$this->load->model('admin_estate_model');
		$this->load->model('reports_model');

	}
	
   function sales_reports()
   {
	  $data['message'] = (validation_errors()) ? validation_errors() : $this->session->flashdata('message');	   
	  $data['users'] = $this->reports_model->getAllUsers();
	  $data['warehouses'] = $this->reports_model->getAllWarehouses();
	  $data['customers'] = $this->reports_model->getAllCustomers();
	  $data['billers'] = $this->reports_model->getAllBillers();
      
      $data['ptitle'] = lang('sale_reports');
      $this->tmadmin->tmView('report_panel/sales', $data);
   }
   
   function getSales()
   {
 		//if($this->input->get('name')){ $name = $this->input->get('name'); } else { $name = NULL; }
		if($this->input->get('user')){ $user = $this->input->get('user'); } else { $user = NULL; }
		if($this->input->get('customer')){ $customer = $this->input->get('customer'); } else { $customer = NULL; }
		if($this->input->get('reference_no')){ $reference_no = $this->input->get('reference_no'); } else { $reference_no = NULL; }
		if($this->input->get('start_date')){ $start_date = $this->input->get('start_date'); } else { $start_date = NULL; }
		if($this->input->get('end_date')){ $end_date = $this->input->get('end_date'); } else { $end_date = NULL; }
		if($start_date) {
                    $start_date = $this->ion_auth->fsd($start_date);
                    $end_date = $this->ion_auth->fsd($end_date);
		}
	   $this->load->library('datatables');
	   $this->datatables
			->select("sales.id as sid,date, reference_no, biller_name, customer_name, GROUP_CONCAT(CONCAT(sale_items.product_name, ' (', sale_items.quantity, ')') SEPARATOR ', <br>') as iname, total_tax,  total", FALSE)
			->from('sales')
			->join('sale_items', 'sale_items.sale_id=sales.id', 'left')
			->join('warehouses', 'warehouses.id=sales.warehouse_id', 'left')
			->group_by('sales.id');
			
			
			if($user) { $this->datatables->like('sales.user', $user); }
			//if($name) { $this->datatables->like('sale_items.product_name', $name, 'both'); }
			if($customer) { $this->datatables->like('sales.customer_id', $customer); }
			if($reference_no) { $this->datatables->like('sales.reference_no', $reference_no, 'both'); }
			if($start_date) { $this->datatables->where('sales.date BETWEEN "'. $start_date. '" and "'.$end_date.'"'); }
			
		/*$this->datatables->add_column("Actions", 
			"<center><a href='#' onClick=\"MyWindow=window.open('index.php?module=sales&view=view_invoice&id=$1', 'MyWindow','toolbar=no,location=no,directories=no,status=no,menubar=no,scrollbars=yes,resizable=yes,width=1000,height=600'); return false;\" title='".$this->lang->line("view_invoice")."' class='tip'><i class='icon-fullscreen'></i></a> 
			<a href='index.php?module=sales&view=pdf&id=$1' title='".$this->lang->line("download_pdf")."' class='tip'><i class='icon-file'></i></a> 
			<a href='index.php?module=sales&view=email_invoice&id=$1' title='".$this->lang->line("email_invoice")."' class='tip'><i class='icon-envelope'></i></a>
			<a href='index.php?module=sales&amp;view=edit&amp;id=$1' title='".$this->lang->line("edit_invoice")."' class='tip'><i class='icon-edit'></i></a>
			<a href='index.php?module=sales&amp;view=delete&amp;id=$1' onClick=\"return confirm('". $this->lang->line('alert_x_invoice') ."')\" title='".$this->lang->line("delete_invoice")."' class='tip'><i class='icon-trash'></i></a></center>", "sid");*/
		
		$this->datatables->unset_column('sid');
		
		
	   echo $this->datatables->generate();
   }
   
   function purchases()
   {
	  $data['message'] = (validation_errors()) ? validation_errors() : $this->session->flashdata('message');	   
	  $data['users'] = $this->reports_model->getAllUsers();
	  $data['warehouses'] = $this->reports_model->getAllWarehouses();
	  $data['suppliers'] = $this->reports_model->getAllSuppliers();
      $this->session->set_userdata('page', 19);
      $data['ptitle'] = lang('purchase_reports');
      $this->tmadmin->tmView('report_panel/purchases', $data);
   }
   
   function getPurchases()
   {
 		//if($this->input->get('name')){ $name = $this->input->get('name'); } else { $name = NULL; }
		if($this->input->get('user')){ $user = $this->input->get('user'); } else { $user = NULL; }
		if($this->input->get('supplier')){ $supplier = $this->input->get('supplier'); } else { $supplier = NULL; }
		if($this->input->get('warehouse')){ $warehouse = $this->input->get('warehouse'); } else { $warehouse = NULL; }
		if($this->input->get('reference_no')){ $reference_no = $this->input->get('reference_no'); } else { $reference_no = NULL; }
		if($this->input->get('start_date')){ $start_date = $this->input->get('start_date'); } else { $start_date = NULL; }
		if($this->input->get('end_date')){ $end_date = $this->input->get('end_date'); } else { $end_date = NULL; }
		if($start_date) {
                    $start_date = $this->ion_auth->fsd($start_date);
                    $end_date = $this->ion_auth->fsd($end_date);
		}
	   $this->load->library('datatables');
	   $this->datatables
			->select("purchases.id as id, date, reference_no, warehouses.name as wname, supplier_name, GROUP_CONCAT(CONCAT(purchase_items.product_name, ' (', purchase_items.quantity, ')') SEPARATOR ', <br>') as iname, COALESCE(inv_total, 0), COALESCE(total_tax, 0), total", FALSE)
			->from('purchases')
			->join('purchase_items', 'purchase_items.purchase_id=purchases.id', 'left')
			->join('warehouses', 'warehouses.id=purchases.warehouse_id', 'left')
			->group_by('purchases.id');
			
			if($user) { $this->datatables->like('purchases.user', $user); }
			//if($name) { $this->datatables->like('purchase_items.product_name', $name); }
			if($supplier) { $this->datatables->like('purchases.supplier_id', $supplier); }
			if($warehouse) { $this->datatables->like('purchases.warehouse_id', $warehouse); }
			if($reference_no) { $this->datatables->like('purchases.reference_no', $reference_no, 'both'); }
			if($start_date) { $this->datatables->where('purchases.date BETWEEN "'. $start_date. '" and "'.$end_date.'"'); }
			
		$this->datatables->add_column("Actions", 
			"<center><a href='#' onClick=\"MyWindow=window.open('index.php?module=inventories&view=view_inventory&id=$1', 'MyWindow','toolbar=no,location=no,directories=no,status=no,menubar=no,scrollbars=yes,resizable=yes,width=1000,height=600'); return false;\" title='".$this->lang->line("view_inventory")."' class='tip'><i class='icon-fullscreen'></i></a> <a href='index.php?module=inventories&view=pdf&id=$1' title='".$this->lang->line("download_pdf")."' class='tip'><i class='icon-file'></i></a> <a href='index.php?module=inventories&view=email_inventory&id=$1' title='".$this->lang->line("email_inventory")."' class='tip'><i class='icon-envelope'></i></a> <a href='index.php?module=inventories&amp;view=edit&amp;id=$1' title='".$this->lang->line("edit_inventory")."' class='tip'><i class='icon-edit'></i></a> <a href='index.php?module=inventories&amp;view=delete&amp;id=$1' onClick=\"return confirm('". $this->lang->line('alert_x_inventory') ."')\" title='".$this->lang->line("delete_inventory")."' class='tip'><i class='icon-trash'></i></a></center>", "id")
		->unset_column('id');
		
	   echo $this->datatables->generate();
   }
   
   function daily_sales()
   {
   		$this->session->set_userdata('page', 19);
	   if($this->input->get('year')){ $year = $this->input->get('year'); } else { $year = date('Y'); }
	   if($this->input->get('month')){ $month = $this->input->get('month'); } else { $month = date('m'); }
	  
	   $data['message'] = (validation_errors()) ? validation_errors() : $this->session->flashdata('message');
	   	
		$config['translated_day_names'] = array($this->lang->line("sunday"), $this->lang->line("monday"), $this->lang->line("tuesday"), $this->lang->line("wednesday"), $this->lang->line("thursday"), $this->lang->line("friday"), $this->lang->line("saturday"));
		$config['translated_month_names'] = array('01' => $this->lang->line("january"), '02' => $this->lang->line("february"), '03' => $this->lang->line("march"), '04' => $this->lang->line("april"), '05' => $this->lang->line("may"), '06' => $this->lang->line("june"), '07' => $this->lang->line("july"), '08' => $this->lang->line("august"), '09' => $this->lang->line("september"), '10' => $this->lang->line("october"), '11' => $this->lang->line("november"), '12' => $this->lang->line("december"));

		$config['template'] = '

   			{table_open}<table border="0" cellpadding="0" cellspacing="0" class="table table-bordered">{/table_open}
			
			{heading_row_start}<tr>{/heading_row_start}
			
			{heading_previous_cell}<th style="text-align:center;"><a href="{previous_url}">&lt;&lt;</a></th>{/heading_previous_cell}
			{heading_title_cell}<th colspan="{colspan}" id="month_year" style="text-align:center;">{heading}</th>{/heading_title_cell}
			{heading_next_cell}<th style="text-align:center;"><a href="{next_url}">&gt;&gt;</a></th>{/heading_next_cell}
			
			{heading_row_end}</tr>{/heading_row_end}
			
			{week_row_start}<tr>{/week_row_start}
			{week_day_cell}<td class="cl_wday" style="text-align:center;">{week_day}</td>{/week_day_cell}
			{week_row_end}</tr>{/week_row_end}
			
			{cal_row_start}<tr class="days">{/cal_row_start}
			{cal_cell_start}<td class="day">{/cal_cell_start}
			
			{cal_cell_content}
				<div class="day_num">{day}</div>
				<div class="content">{content}</div>
			{/cal_cell_content}
			{cal_cell_content_today}
				<div class="day_num highlight">{day}</div>
				<div class="content">{content}</div>
			{/cal_cell_content_today}
			
			{cal_cell_no_content}<div class="day_num">{day}</div>{/cal_cell_no_content}
			{cal_cell_no_content_today}<div class="day_num highlight">{day}</div>{/cal_cell_no_content_today}
			
			{cal_cell_blank}&nbsp;{/cal_cell_blank}
			
			{cal_cell_end}</td>{/cal_cell_end}
			{cal_row_end}</tr>{/cal_row_end}
			
			{table_close}</table>{/table_close}
';

		$this->load->library('daily_cal', $config);
				 
		$sales = $this->reports_model->getDailySales($year, $month);
		
		$num = cal_days_in_month(CAL_GREGORIAN, $month, $year);
		
		if(!empty($sales)) {
			foreach($sales as $sale){
				$daily_sale[$sale->date] = "<table class='table table-bordered table-hover table-striped table-condensed data' style='margin:0;'><tr><td>".$this->lang->line("tax1")."</td><td>". $this->ion_auth->formatMoney($sale->tax1) ."</td></tr><tr><td>".$this->lang->line("total")."</td><td>". $this->ion_auth->formatMoney($sale->total) ."</td></tr></table>";	
			}
			
		/*for ($i = 1; $i <= $num; $i++){
       		
       			if(isset($cal_data[$i])) {
        			$daily_sale[$i] = $cal_data[$i]; 
				} else { 
					$daily_sale[$i] = $this->lang->line('no_sale');
				}
        	
    	}
		
		
		} else {
			for($i=1; $i<=$num; $i++) {
			$daily_sale[$i] = $this->lang->line('no_sale');
		}*/
		} else { $daily_sale = array(); }
		
	   $data['calender'] = $this->daily_cal->generate($year, $month, $daily_sale);
	   $data['page'] = 19;
	  $data['ptitle'] = lang('daily_sales');
	  $this->tmadmin->tmView('report_panel/daily', $data);
   }
   
  
   function monthly_sales()
   {
	  if($this->input->get('year')){ $year = $this->input->get('year'); } else { $year = date('Y'); }

	  $data['message'] = (validation_errors()) ? validation_errors() : $this->session->flashdata('message');
	 $this->session->set_userdata('page', 19);
	  $data['year'] = $year;
		
	  $data['sales'] = $this->reports_model->getMonthlySales($year);
	   
	  $data['ptitle'] = lang('monthly_sales');
	  $this->tmadmin->tmView('report_panel/monthly', $data);
   }
   
   function custom_products()
   {
	  
	   $data['message'] = (validation_errors() ? validation_errors() : $this->session->flashdata('message'));
	   if($this->input->post('start_date')){ $dt = "From ".$this->input->post('start_date')." to ".$this->input->post('end_date'); } else { $dt = "Till ".$this->input->post('end_date'); }
      //$meta['page_title'] = $this->lang->line("reports")." ".$dt;
	  $data['products'] = $this->reports_model->getAllProducts();
      $meta['page_title'] = $this->lang->line("product_reports")." ".$dt;
	  $data['page_title'] = $this->lang->line("product_reports");
      $this->load->view('commons/header', $meta);
      $this->load->view('products', $data);
      $this->load->view('commons/footer');
   }
   function getCP()
   {
	    if($this->input->get('product')){ $product = $this->input->get('product'); } else { $product = NULL; }
            if($this->input->get('start_date')){ $start_date = $this->input->get('start_date'); } else { $start_date = NULL; }
            if($this->input->get('end_date')){ $end_date = $this->input->get('end_date'); } else { $end_date = NULL; }
            if($start_date) {
                    $start_date = $this->ion_auth->fsd($start_date);
                    $end_date = $this->ion_auth->fsd($end_date);
                    
                $pp =    "( SELECT pi.product_id, SUM( pi.quantity ) purchasedQty from purchases p JOIN purchase_items pi on p.id = pi.purchase_id where
                         p.date >= '{$start_date}' and p.date < '{$end_date}'
                         group by pi.product_id ) PCosts";
                
                $sp = "( SELECT si.product_id, SUM( si.quantity ) soldQty, SUM( si.gross_total ) totalSale from sales s JOIN sale_items si on s.id = si.sale_id where
                       s.date >= '{$start_date}' and s.date < '{$end_date}'
                       group by si.product_id ) PSales";
            } else {
                $pp ="( SELECT pi.product_id, SUM( pi.quantity ) purchasedQty from purchase_items pi group by pi.product_id ) PCosts";
                $sp = "( SELECT si.product_id, SUM( si.quantity ) soldQty, SUM( si.gross_total ) totalSale from sale_items si group by si.product_id ) PSales";
            }
	  
        $this->load->library('datatables');
        $this->datatables
                ->select("p.name,
                        p.code,
                        COALESCE( PCosts.purchasedQty, 0 ) as PurchasedQty,
                        COALESCE( PSales.soldQty, 0 ) as SoldQty,
                        COALESCE( PSales.totalSale, 0 ) as TotalSales,
                        ROUND(COALESCE( PSales.totalSale, 0 ) - ( COALESCE( PSales.soldQty, 0 ) * p.Cost ), 2) as GrossProfit", FALSE)
                ->from('products p', FALSE)
                ->join($sp, 'p.id = PSales.product_id', 'left')
                ->join($pp, 'p.id = PCosts.product_id', 'left');
                       // ->group_by('p.id');

		if($product) { $this->datatables->where('p.id', $product); }	

	   echo $this->datatables->generate();

   }
   
   public function logins()
   {
   	$this->lang->load('fx');
   	$this->load->model('mdl_reports');
   	
   	$data['page'] = $this->lang->line('reports');
   	$data['logs'] = $this->mdl_reports->user_log();
   	$this->session->set_userdata('page', 19);
   	$data['ptitle'] = lang('reports');
   	$this->tmadmin->tmView('report_panel/logins', $data);
   }
   
   public function payments()
   {
   		$this->load->model('mdl_reports');
   		
	   	$graph_data = $this->_data();
	   	$this->lang->load('fx');
	   	$this->load->library('highcharts');
	   	$this->load->helper('analytics');

	   	$data['page'] = $this->lang->line('reports');
	   	
	   	$this->highcharts->set_title('MONTHLY STATISCTICS', 'Monthly Payments'); // set chart title: title, subtitle(optional)
	   	$this->highcharts->set_axis_titles('Month', 'Amount Received'); // axis titles: x axis,  y axis
	   	$serie['data'] = array(
	   			$this->_monthly_data('01'),
	   			$this->_monthly_data('02'),
	   			$this->_monthly_data('03'),
	   			$this->_monthly_data('04'),
	   			$this->_monthly_data('05'),
	   			$this->_monthly_data('06'),
	   			$this->_monthly_data('07'),
	   			$this->_monthly_data('08'),
	   			$this->_monthly_data('09'),
	   			$this->_monthly_data('10'),
	   			$this->_monthly_data('11'),
	   			$this->_monthly_data('12')
	   	);
	   	$this->highcharts->set_xAxis($graph_data['axis']); // pushing categories for x axis labels
	   	$data['charts'] = $this->highcharts->set_serie($serie,'Amount Rec.')->render();
	   	$this->session->set_userdata('page', 19);
	   	$data['ptitle'] = lang('payments');
	   	$this->tmadmin->tmView('report_panel/payments', $data);
   }
   
   function _data()
   {
	   	$data['axis']['categories'] = array('Jan', 'Feb', 'Mar', 'Apr', 'May','Jun', 'Jul', 'Aug', 'Sep','Oct','Nov','Dec');
	   	return $data;
   }
   function _monthly_data($month)
   {
   	$this->load->model('mdl_reports');
	   	$this->db->select_sum('paid_amount');
	   	$this->db->where('month', $month);
	   	$query = $this->db->get('payments');
	   	foreach ($query->result() as $row)
	   	{
	   		$amount = $row->paid_amount ? $row->paid_amount : 0;
	   		return floor($amount);
	   	}
   }
}